Repeatable Data Import

The file format of a repeatable import is usually defined by the system the data is being extracted from i.e. Oracle, Agresso etc. The preferred file format is either .CSV or Excel.

For a repeatable import, a bespoke routine is written which will then allow the Verto System Administrator to take the exported data from the file and import it directly into the relevant fields in Verto, as often as needed. 

Standard Process

A standard repeatable import process works as follows:

1. Generate a file from the other system containing all the required data to be imported into Verto.
2. Label each column in the file with the corresponding field in Verto i.e. where the data is to be imported to. The labels should be the original database names.
3. Review all look-up answers and names within the file to ensure that they exist in Verto. 
4. Send a copy of the data import file to Verto Support along with details of any rules for the import. This will then be assessed and a quote provided.
5. Upon agreement of the quote, work will commence to create the repeatable import routine.
6. Upon completion, a version will be added to the Test site for user acceptance testing.
7. When sign-off is received, this will be switched on in the Live site and can be accessed via the Administration Cog > Import Costs, as often as needed. 

Guidance Notes

• For data to be imported into Verto on a regular basis there must be a unique identifier that exists in both systems e.g. Project Code, Risk ID, PO Number etc. The most common unique identifier is the Verto project code, although depending on the type of data being imported, an additional lower level identifier may also be required i.e. matching specific cost lines within a project. These unique identifiers will ensure the import knows which records within the project to update with the new information.
• Each line on the spreadsheet should represent a new record to be imported i.e. a risk, cost etc.
• All columns in the spreadsheet must be clearly labelled with the corresponding original database field name- not the front end label. This is to ensure the data is imported into the correct fields.
• All look-ups in the spreadsheet must match the look-up answers in Verto (including case sensitivity and spacing between words), i.e. if a look-up answer is ‘united kingdom’ in Verto but ‘United Kingdom’ in the file, this will not import. All answers which do not match exactly to a corresponding look-up value in the system should be removed from the import file. 
• All users in the spreadsheet must already exist in the database. Users without an account in Verto will not be imported.
• All names in the spreadsheet must be identical to the user name in Verto i.e. John Smith will not map to Jonathan Smith. The format should also be FirstName Surname. Email addresses can be used instead of FirstName Surname to improve success of matching - especially if there could be 2 users with the same name.
• There should only ever be one piece of data in each cell i.e. if there are 3 individuals to be imported against a project, then these 3 individuals must each be listed in a separate column/cell or can be recorded in a separate worksheet which is linked back to the project (as covered above). 
• All dates should be written in the following format ‘31/01/2022’ to match how they are recorded in Verto.
• Data consistency should be reviewed throughout the file i.e. words such as N/A, TBC, Refer to John etc should be removed unless being mapped to a text field.